package com.crishne.anwo.utill;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelHelper {
	/*
	 * 读取excel
	 */
	public static void doIt(String xlsFile) {
		System.out.println(xlsFile);
		try {
			FileInputStream fis = new FileInputStream(xlsFile);
			//POIFSFileSystem fs = new POIFSFileSystem(fis);
			XSSFWorkbook wb = new XSSFWorkbook(fis);
			XSSFSheet itemMainSheet = wb.getSheetAt(0);
			int rowCount = itemMainSheet.getPhysicalNumberOfRows();
			//HashSet<String> addedDeptNameSet = new HashSet<String>();
			
			System.out.println("总数据量："+rowCount);
			int count1=0,countmen=0,countWomen=0,countOther=0; 
			String preOpenid="";
			String crrentOpenid="";
			for ( int i = 1; i < rowCount; i++ ) {
				XSSFRow row = itemMainSheet.getRow(i);
				crrentOpenid = row.getCell(2).getStringCellValue().trim();
				String sex = row.getCell(4).getStringCellValue().trim();
				if(i==1){
					preOpenid = crrentOpenid; 
					count1=1;
					if("男".equals(sex)){
						countmen = 1;
					}
					else if("女".equals(sex)){
						countWomen = 1;
					}
					else{
						countOther = 1;
					}
				}
				else{
					if(!crrentOpenid.equals(preOpenid)){
						preOpenid = crrentOpenid;
						++count1;//总人数
						
						if("男".equals(sex)){
							++countmen;
						}
						else if("女".equals(sex)){
							++countWomen;
						}
						else{
							++countOther;
						}
					}
				}
				
//				String cell0 = getCellStringValue(row, 0);
//				String cell1 = getCellStringValue(row, 1);
//				String cell2 = getCellStringValue(row, 2);
//				System.out.println("第"+(i+1)+"列数据如下：");
//				System.out.println(cell0+"-"+cell1+"-"+cell2);
			}
			System.out.println("总人数："+count1);
			System.out.println("男性人数："+countmen);
			System.out.println("女性人数："+countWomen);
			System.out.println("其他人数："+countOther);
			wb.close();
		}catch (Exception ex) {
			System.out.println("报错信息："+ex.getMessage());
			ex.printStackTrace();
		}
	}
	
	// 创建EXCEL文件
	private static void createExcel(String desDir) {
	    FileOutputStream fos = null;
	    try {
	        fos = new FileOutputStream(desDir);
	        
	        XSSFWorkbook wb = new XSSFWorkbook();
	        XSSFSheet sheet = wb.createSheet();
	        wb.setSheetName(0, "游戏玩家统计");
	        XSSFRow header = sheet.createRow(0);
	        header.createCell(0).setCellValue("序号");
	        header.createCell(1).setCellValue("男性人数");
	        header.createCell(2).setCellValue("女性人数");
	        header.createCell(3).setCellValue("游客玩家");
	       
	        
	        XSSFRow row = sheet.createRow(1);
	        
	        row.createCell(0).setCellValue("1");
	        row.createCell(1).setCellValue("bbbbbbb");
	        row.createCell(2).setCellValue("你好");
	        row.createCell(3).setCellValue("非常好");
	        
	        wb.write(fos);
	        wb.close();
	        fos.close();
	    } catch (Exception ex) {
	        ex.printStackTrace();
	    } finally {
	        try {
	            if (null != fos) {
	                fos.close();
	            }
	        } catch (Exception ex) {
	        }
	    }
	}
	
	/*
	 * 读取excel，并生成insert sql语句
	 */
	public static void doItInsert(String xlsFile) {
		try {
			FileInputStream fis = new FileInputStream(xlsFile);
			//POIFSFileSystem fs = new POIFSFileSystem(fis);
			XSSFWorkbook wb = new XSSFWorkbook(fis);
			XSSFSheet itemMainSheet = wb.getSheetAt(0);
			int rowCount = itemMainSheet.getPhysicalNumberOfRows();
			//HashSet<String> addedDeptNameSet = new HashSet<String>();
			
			System.out.println("总数据量："+rowCount);
			int count1=0,countmen=0,countWomen=0,countOther=0; 
			for ( int i = 1; i < rowCount; i++ ) {
				StringBuilder sqlBuilder = new StringBuilder("INSERT INTO `nkyd_pointstore_record`(`id`, `pointcost`, `storeid`, `title`, `uid`, `addtime`, `mobile`, `realname`, `address`, `notes`) "); 
				sqlBuilder.append("VALUES ("); 
				
				XSSFRow row = itemMainSheet.getRow(i);
				sqlBuilder.append(String.valueOf((int)row.getCell(0).getNumericCellValue())+",");
				sqlBuilder.append(String.valueOf((int)row.getCell(4).getNumericCellValue())+",");
				sqlBuilder.append(String.valueOf(i)+",");
				sqlBuilder.append("'"+row.getCell(3).getStringCellValue().trim()+"',");
				sqlBuilder.append(String.valueOf(i)+",");
				
				SimpleDateFormat sdf =   new SimpleDateFormat( "yyyy/MM/dd" );
				Date setdate = sdf.parse(row.getCell(8).getStringCellValue().trim());
				
				sqlBuilder.append(setdate.getTime()/1000+",");
				
				sqlBuilder.append("'"+row.getCell(5).getStringCellValue().trim()+"',");
				sqlBuilder.append("'"+row.getCell(2).getStringCellValue().trim()+"',");
				sqlBuilder.append("'"+row.getCell(6).getStringCellValue().trim()+"',");
				sqlBuilder.append("'');");
				
				
				
//				String cell0 = getCellStringValue(row, 0);
//				String cell1 = getCellStringValue(row, 1);
//				String cell2 = getCellStringValue(row, 2);
//				System.out.println("第"+(i+1)+"列数据如下：");
				System.out.println(sqlBuilder.toString());
			}
			System.out.println("总人数："+count1);
			System.out.println("男性人数："+countmen);
			System.out.println("女性人数："+countWomen);
			System.out.println("其他人数："+countOther);
			wb.close();
		}catch (Exception ex) {
			System.out.println("报错信息："+ex.getMessage());
			ex.printStackTrace();
		}
	}

	
//	private static String getCellStringValue(XSSFRow row, int cellIndex) {
//		XSSFCell cell = row.getCell(cellIndex);
//		if ( XSSFCell.CELL_TYPE_STRING == cell.getCellType() ) {
//			return cell.getStringCellValue().trim();
//		}
//		if ( XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType() ) {
//			String str = NumberFormat.getNumberInstance().format(cell.getNumericCellValue());
//			while(str.indexOf(",")>-1){
//				str = str.substring(0,str.indexOf(","))+str.substring(str.indexOf(",")+1);
//			}
//			return str;
//		}
//		return null;
//	}
	
	
	public static void main(String[] args){
		System.out.println("开始读取文件");
		doItInsert("D:\\test\\aa.xlsx");
		System.out.println("结束读取");
		
		
//		System.out.println("开始创建文件");
//		createExcel("D:\\bb.xlsx");
//		System.out.println("创建结束");
	}

}
